package com.glowdayz.glowmee.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.DateTime;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.CommonBannerCMD;

public class CommonBannerDAO {
	
	static Logger logger = Logger.getLogger(CommonBannerDAO.class);
	
	private static CommonBannerDAO instance = null;
	
	DBHandler db = null;
	
	public static CommonBannerDAO getInstance() {
		if (instance == null) {
			instance = new CommonBannerDAO();
		}
		return instance;
	}
	
	public CommonBannerDAO() {
		db = DBHandler.getInstance();
	}
	
	/**
	 * 공통배너 추가
	 * @param cmd
	 * @return int
	 */
	public int insertCommonBanner(CommonBannerCMD cmd) throws Exception {
		logger.debug("[insertCommonBanner] ---------- Method Start ----------");
		logger.debug("[insertCommonBanner][PARAM] CommonBannerCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		PreparedStatement pstmt = null;
		
		String sql = "INSERT INTO commonbanner("
				+ "bannerType, bannerTitle, bannerLinkType, bannerLinkCode, start_date, end_date, sortKey, isDisplay, create_date"
				+ ") VALUES (?, ?, ?, ?, ?, ?, ?, 1, ?)";
		
		pstmt = db.getPrepareStatement(sql);
		
		DBHandler.getInstance().beginTrans();
		try {
			pstmt.setString(1, cmd.getBannerType());
			pstmt.setString(2, cmd.getBannerTitle());
			pstmt.setInt(3, cmd.getBannerLinkType());
			pstmt.setString(4, cmd.getBannerLinkCode());
			pstmt.setString(5, cmd.getStart_date());
			pstmt.setString(6, cmd.getEnd_date());
			pstmt.setInt(7, cmd.getSortKey());
			pstmt.setString(8, DateTime.getInstance().getNowDateTimeString());
			result = pstmt.executeUpdate();
			
			if (result > 0) {
				// 자동증가 일련변호 조회
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					result = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			
			pstmt.close();
			pstmt = null;
			
			if (cmd.getImage() != null) {
				
				String fileSql = "INSERT INTO attachefile ("
						+ "tableName, tableKey, fileOrgName, fileSaveName, fileDir, fileSize, fileType, isDisplay, create_date, isUser"
						+ ") VALUES ('commonbanner', ?, ?, ?, ?, ?, ?, 1, ?, 0)";
				
				int fileResult = 0;
				
				FileInfo item = cmd.getImage();
				
				pstmt = db.getPrepareStatement(fileSql);
				pstmt.setInt(1, result);
				pstmt.setString(2, item.getFileOrgName());
				pstmt.setString(3, item.getFileSaveName());
				pstmt.setString(4, item.getFileDir());
				pstmt.setLong(5, item.getFileSize());
				pstmt.setString(6, item.getFileType());
				pstmt.setString(7, DateTime.getInstance().getNowDateTimeString());
				
				fileResult = pstmt.executeUpdate();
				
				if (fileResult > 0) {
					// 자동증가 일련변호 조회
					ResultSet rs = pstmt.getGeneratedKeys();
					if (rs.next()) {
						fileResult = rs.getInt(1);
					}
					rs.close();
					rs = null;
				} else {
					// 모든 데이터 저장 취소
					pstmt.close();
					pstmt = null;
					DBHandler.getInstance().rollback();
					return -1;
				}
				
				pstmt.close();
				pstmt = null;
				
				String update = "UPDATE commonbanner "
						+ "SET bannerImg=? "
						+ "WHERE idBanner=?";
				pstmt = db.getPrepareStatement(update);
				pstmt.setInt(1, fileResult);
				pstmt.setInt(2, result);
				result = pstmt.executeUpdate();
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[insertCommonBanner] " + e.getLocalizedMessage());
			logger.error("[insertCommonBanner] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[insertCommonBanner][RETURN] result=" + result);
		logger.debug("[insertCommonBanner] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 공통배너 수정
	 * @param cmd
	 * @return int
	 */
	public int updateCommonBanner(CommonBannerCMD cmd) throws SQLException {
		logger.debug("[updateCommonBanner] ---------- Method Start ----------");
		logger.debug("[updateCommonBanner][PARAM] CommonBannerCMD cmd=" + cmd.toString());
		
		int result = 0;
		
		DBHandler.getInstance().beginTrans();
		PreparedStatement pstmt = null;
		
		// (3) 게시물 정보 갱신
		String sql = "UPDATE commonbanner "
				+ "SET bannerType=?, bannerTitle=?, bannerLinkType=?, bannerLinkCode=?, start_date=?, end_date=?, sortKey=?, isDisplay=?, modified_date=? "
				+ "WHERE idBanner=?";
		pstmt = db.getPrepareStatement(sql);
		
		try {
			pstmt.setString(1, cmd.getBannerType());
			pstmt.setString(2, cmd.getBannerTitle());
			pstmt.setInt(3, cmd.getBannerLinkType());
			pstmt.setString(4, cmd.getBannerLinkCode());
			pstmt.setString(5, cmd.getStart_date());
			pstmt.setString(6, cmd.getEnd_date());
			pstmt.setInt(7, cmd.getSortKey());
			pstmt.setInt(8, cmd.getIsDisplay());
			pstmt.setString(9, DateTime.getInstance().getNowDateTimeString());
			pstmt.setInt(10, cmd.getIdBanner());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			DBHandler.getInstance().rollback();
			logger.error("[updateCommonBanner] " + e.getLocalizedMessage());
			logger.error("[updateCommonBanner] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result > 0) {
			DBHandler.getInstance().commit();
		} else {
			DBHandler.getInstance().rollback();
		}
		
		logger.debug("[updateCommonBanner][RETURN] result=" + result);
		logger.debug("[updateCommonBanner] ---------- Method End ----------");
		
		return result;
	}
	
	/**
	 * 공통배너 리스트
	 * @param page
	 * @param count
	 * @param where
	 * @return ArrayList<CommonBannerCMD>
	 */
	public ArrayList<CommonBannerCMD> getCommonBannerList(int page, int count, String where) throws Exception {
		logger.debug("[getCommonBannerList] ---------- Method Start ----------");
		logger.debug("[getCommonBannerList][PARAM] page=" + page);
		logger.debug("[getCommonBannerList][PARAM] count=" + count);
		logger.debug("[getCommonBannerList][PARAM] where=" + where);
		
		ArrayList<CommonBannerCMD> result = null;
		int cnt = db.getCount("commonbanner", "idBanner", where);
		if (count == -1) {
			count = cnt;
		}
		
		String sql = null;
		if (where == null) {
			sql = "SELECT idBanner, bannerType, bannerTitle, bannerLinkType, bannerLinkCode, start_date, end_date, sortKey, isDisplay "
					+ "FROM commonbanner "
					+ "ORDER BY sortKey, create_date DESC "
					+ "LIMIT ?, ?";
		} else {
			sql = "SELECT idBanner, bannerType, bannerTitle, bannerLinkType, bannerLinkCode, start_date, end_date, sortKey, isDisplay "
					+ "FROM commonbanner "
					+ "WHERE " + where + " "
					+ "ORDER BY sortKey, create_date DESC "
					+ "LIMIT ?, ?";
		}
			
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		
		if (cnt == 0) {
			PageHandler.getInstance(page, cnt);
		} else {
			// 페이징 처리 --> 한 페이지에 count개씩, 페이지번호는 10개 출력
			PageHandler.getInstance(page, cnt, count, 10);
		}
		
		try {
			pstmt.setInt(1, PageHandler.getInstance().getStartRec());
			pstmt.setInt(2, PageHandler.getInstance().getPageArticleCount());
			ResultSet rs = pstmt.executeQuery();
			
			if (rs != null) {
				
				// SQL문장에 이상이 없을 경우에만 list 객체를 할당한다.
				// 이 객체가 null이라면 SQL실행에 실패한 경우이다.
				// 객체가 생성되지만, add()가 실행되지 않아서, size()값이 0이라면
				// 조건에 맞는 데이터가 없는 경우이다.
				result = new ArrayList<CommonBannerCMD>();
				
				while(rs.next()) {
					int idBanner = rs.getInt(1);
					String bannerType = rs.getString(2);
					String bannerTitle = rs.getString(3);
					int bannerLinkType = rs.getInt(4);
					String bannerLinkCode = rs.getString(5);
					String start_date = rs.getString(6);
					String end_date = rs.getString(7);
					int sortKey = rs.getInt(8);
					int isDisplay = rs.getInt(9);
					
					FileInfo image = this.getImage(idBanner);
					CommonBannerCMD item = new CommonBannerCMD(idBanner, bannerType, bannerTitle, bannerLinkType, bannerLinkCode, start_date, end_date, sortKey, isDisplay, image);
					result.add(item);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error("[getCommonBannerList] " + e.getLocalizedMessage());
			logger.error("[getCommonBannerList] " + e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		
		if (result != null) {
			logger.debug("[getCommonBannerList][RETURN] result_count=" + result.size());
		}
		logger.debug("[getCommonBannerList] ---------- Method End ----------");
		
		return result;
	}
	
	public FileInfo getImage(int idx) throws SQLException {
		String fileSql = "SELECT fileOrgName, idAttacheFile, fileSaveName, fileDir, fileSize, fileType "
				+ "FROM attachefile "
				+ "WHERE tableName='commonbanner' AND tableKey=?";
		
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(fileSql);
		FileInfo info = null;
		
		try {
			pstmt.setInt(1, idx);
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				while(rs.next()) {
					String fileOrgName = rs.getString(1);
					int idAttacheFile = rs.getInt(2);
					String fileSaveName = rs.getString(3);
					String fileDir = rs.getString(4);
					int fileSize = rs.getInt(5);
					String fileType = rs.getString(6);
					
					info = new FileInfo("", idAttacheFile, fileOrgName, fileSaveName, fileDir, fileSize, fileType);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {
			logger.error(e.getLocalizedMessage());
			logger.error(e.getSQLState());
		} finally {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		}
		return info;
	}
	
}
